const Router = require('koa-router');
let router = new Router();
router.get('', async ctx =>{
    let banner = await ctx.db.query(`SELECT * FROM banner_table ORDER BY serial ASC`);
    let catalog = await ctx.db.query(`SELECT * FROM catalog_table`);
    let article = await ctx.db.query(`SELECT *,
articale_table.title AS articale_title,
catalog_table.title AS catalog_title,
articale_table.ID AS articale_ID FROM articale_table LEFT JOIN catalog_table ON articale_table.catalog_ID=catalog_table.ID
ORDER BY articale_table.created_time DESC LIMIT 10`);
    let {HTTP_ROOT} = ctx.config;
    function famateDate(n) {
        return n>10 ? n : '0'+n
    }
    article.forEach(item=>{
        let oDate = new Date(item.created_time*1000);
        item.created_time = `${oDate.getFullYear()}-${famateDate(oDate.getMonth()+1)}-${famateDate(oDate.getDate())}`
    })
    await ctx.render('www/index',{
        HTTP_ROOT,
        banner,
        catalog,
        article
    });
});
router.get('list/:id/', async ctx =>{
    let {id} = ctx.params;
    let {HTTP_ROOT} = ctx.config;
    let rows = await ctx.db.query(`SELECT * FROM catalog_table WHERE ID=?`,[id]);
    let banner = await ctx.db.query(`SELECT * FROM banner_table ORDER BY serial ASC`);
    let articles = await ctx.db.query(`SELECT *,
        articale_table.title AS articale_title,
        catalog_table.title AS catalog_title,
        articale_table.ID AS articale_ID FROM articale_table LEFT JOIN catalog_table ON articale_table.catalog_ID=catalog_table.ID
        WHERE articale_table.catalog_ID = ${id}
        ORDER BY articale_table.created_time DESC LIMIT 10`);
    function famateDate(n) {
        return n>10 ? n : '0'+n
    }
    articles.forEach(item=>{
        let oDate = new Date(item.created_time*1000);
        item.created_time = `${oDate.getFullYear()}-${famateDate(oDate.getMonth()+1)}-${famateDate(oDate.getDate())}`
    })
    await ctx.render('www/list',{
        HTTP_ROOT,
        banner,
        catalog_title: rows[0].title,
        articles
    });
});
router.get('articles/:id',async ctx =>{
    let {id} = ctx.params;
    let {HTTP_ROOT} = ctx.config;
    let banner = await ctx.db.query(`SELECT * FROM banner_table ORDER BY serial ASC`);
    let rows = await ctx.db.query(`SELECT * FROM articale_table WHERE ID=?`,[id]);
    function famateDate(n) {
        return n>10 ? n : '0'+n
    }
    let articles = rows[0];
    let oDate = new Date(articles.created_time*1000);
    articles.created_time = `${oDate.getFullYear()}-${famateDate(oDate.getMonth()+1)}-${famateDate(oDate.getDate())}`;
    await ctx.render('www/articles',{
        HTTP_ROOT,
        banner,
        articles
    });
})
module.exports = router.routes();